


/* Frandsen et al. test */


use "G:\Data\Workdata\707677\Common\01Data\02STATA\panel_iv", clear 

cd "G:\Data\Workdata\707677\FKYY7677\Debt_relief\Statafiles\Outputs\Revision\FINAL"
keep if time==1


local Xvar_short "male age_0_40 age_41_50 age_51_60 age_61_70 erhvervsindk_mean employed_pstill_mean unemployed_pstill_mean legmarried_m1 racedum1 housedum1 passiv_mean aktiv_mean higher_secondary university social_mean edu_missing housing_missing timelon_dum1 timelon_dum2 timelon_dum3 timelon_dum4 house_m1"


egen lawyer_id_unique =group(lawyerid court) // Unique lawyer id within courts. some lawyers work in multiple courts
bys lawyer_id_unique: egen n_lawyer=count(lawyer_id_unique)
egen mean_lawyer_leniency= mean(granted), by(lawyer_id_unique)

gen iv_judge2_all = (mean_lawyer_leniency*n_lawyer - granted)/(n_lawyer-1)

gen iv_lawyer2=iv_judge2_all-(iv_court*obs_court-granted)/(obs_court-1)



tab obs_lawyer
tab n_lawyer

keep if n_lawyer>=20

egen n_judge=nvals(lawyer_id_unique), by(court aar)



keep if n_judge>1

tab mean_lawyer_leniency

keep pnr iv_lawyer2 lawyer_id_unique n_lawyer


merge 1:m pnr using "G:\Data\Workdata\707677\Common\01Data\02STATA\panel_iv"
keep if _merge==3
drop _merge

keep if time>0

tab lawyer_id_unique, gen(lawyer_dum)


egen court_pyear =group(court pyear) 
tab court_pyear, gen(court_dum)

tab time

tab aar, gen(aardum)

tab court, gen(courts)


* all

gen all=1


tab obs_court

keep if obs_court>900
capture drop freq_court
bysort court: egen freq_court=count(court)

tab freq_court

drop if freq_court<200
tab court


local Xvar_short "male age_0_40 age_41_50 age_51_60 age_61_70 erhvervsindk_mean employed_pstill_mean unemployed_pstill_mean legmarried_m1 racedum1 housedum1 passiv_mean aktiv_mean higher_secondary university social_mean edu_missing housing_missing timelon_dum1 timelon_dum2 timelon_dum3 timelon_dum4 house_m1"	




	* By court 
	
	
	capture drop court_name
 gen court_name="KBH" if freq_court==64848
   replace court_name="AARHUS" if freq_court==42720
     replace court_name="AAlBORG" if freq_court==23616
	 replace court_name="ODENSE" if freq_court==21840
 replace court_name="ROSKILDE" if freq_court==19856
	replace court_name="HJORRING" if freq_court==15888
	replace court_name="RANDERS" if freq_court==13648
		replace court_name="HERNING" if freq_court==13184
		   replace court_name="HORSENS" if freq_court==10704
		      replace court_name="SVENDBORG" if freq_court==10064
  
	local depvar  erhvervsindk_real employed_pstill aktiv_real  formue_real bankgaeld_real mortgage_real

	


	foreach var of varlist `depvar' {
	
	
	local Xvar_short "male age_0_40 age_41_50 age_51_60 age_61_70 erhvervsindk_mean employed_pstill_mean unemployed_pstill_mean legmarried_m1 racedum1 housedum1 passiv_mean aktiv_mean higher_secondary university social_mean edu_missing housing_missing timelon_dum1 timelon_dum2 timelon_dum3 timelon_dum4 house_m1"	

	  	putexcel set "frandsen_`var'.xlsx",  replace
	
		
		putexcel B3="København"
		putexcel C3="Aarhus"
	 	putexcel D3="Aalborg"
		putexcel E3="Odense"
		putexcel F3="Roskilde"
		putexcel G3="Hjorring"
		putexcel H3="Randers"
		putexcel I3="Herning"
		putexcel J3="Horsens"
		putexcel K3="Svendborg"
	
	
local j=3
local f=3
  forvalues i=1/16 {
  local	j=1+`f'
  local d=`j'+1
  local f = `d'+1
	putexcel A`j'=`i'
	

  	  testjfe `var' granted lawyer_dum* if court_name=="KBH" & time==`i', cov(aardum* `Xvar_short') fitweight(1) 	numknots(3) 
	  putexcel B`j' =(r(pval))
	  putexcel B`d' =(r(df))
	    putexcel B`f' =(r(chi2))
	  
	
	  testjfe `var' granted lawyer_dum* if court_name=="AARHUS" & time==`i', cov(aardum* `Xvar_short') fitweight(1) 	numknots(3) 
	    putexcel C`j' =(r(pval))
			  putexcel C`d' =(r(df))
	    putexcel C`f' =(r(chi2))
	  
  testjfe `var' granted lawyer_dum* if court_name=="AAlBORG" & time==`i', cov(aardum* `Xvar_short') fitweight(1) numknots(3)
    putexcel D`j' =(r(pval))
		  putexcel D`d' =(r(df))
	    putexcel D`f' =(r(chi2))
	  
  testjfe `var' granted lawyer_dum* if court_name=="ODENSE" & time==`i', cov(aardum* `Xvar_short') fitweight(1)	numknots(3) 
    putexcel E`j' =(r(pval))
		  putexcel E`d' =(r(df))
	    putexcel E`f' =(r(chi2))
	  
  testjfe `var' granted lawyer_dum* if court_name=="ROSKILDE"  & time==`i', cov(aardum* `Xvar_short') fitweight(1) 	numknots(3) 
    putexcel F`j' =(r(pval))
		  putexcel F`d' =(r(df))
	    putexcel F`f' =(r(chi2))
	  
  testjfe `var' granted lawyer_dum* if court_name=="HJORRING" & time==`i', cov(aardum* `Xvar_short') fitweight(1) 	numknots(3) 
    putexcel G`j' =(r(pval))
		  putexcel G`d' =(r(df))
	    putexcel G`f' =(r(chi2))
	  
  testjfe `var' granted lawyer_dum* if court_name=="RANDERS" & time==`i', cov(aardum* `Xvar_short') fitweight(1) numknots(3) 
    putexcel H`j' =(r(pval))
		  putexcel H`d' =(r(df))
	    putexcel H`f' =(r(chi2))
	  
  testjfe `var' granted lawyer_dum* if court_name=="HERNING" & time==`i', cov(aardum* `Xvar_short') fitweight(1) 	numknots(3) 
    putexcel I`j' =(r(pval))
		  putexcel I`d' =(r(df))
	    putexcel I`f' =(r(chi2))
	  
  testjfe `var' granted lawyer_dum* if  court_name=="HORSENS" & time==`i', cov(aardum* `Xvar_short') fitweight(1) numknots(3) 
    putexcel J`j' =(r(pval))
		  putexcel J`d' =(r(df))
	    putexcel J`f' =(r(chi2))
	  
  testjfe `var' granted lawyer_dum* if court_name=="SVENDBORG" & time==`i', cov(aardum* `Xvar_short') fitweight(1) numknots(3) 
  putexcel K`j' =(r(pval))
  	  putexcel K`d' =(r(df))
	    putexcel K`f' =(r(chi2))
	  

}
	
	local Xvar_short "male age_0_40 age_41_50 age_51_60 age_61_70 erhvervsindk_mean employed_pstill_mean unemployed_pstill_mean legmarried_m1 racedum1 housedum1 passiv_mean aktiv_mean higher_secondary university social_mean edu_missing housing_missing timelon_dum1 timelon_dum2 timelon_dum3 timelon_dum4 house_m1"	

	
local j = `f'+1	
  local d=`j'+1
  local f = `d'+1
	
	
putexcel A`j'="all"
  	  testjfe `var' granted lawyer_dum* if court_name=="KBH", cov(aardum* `Xvar_short') fitweight(1) 	numknots(3) 
	  putexcel B`j' =(r(pval))
	    putexcel B`d' =(r(df))
	    putexcel B`f' =(r(chi2))
	  
	
	  testjfe `var' granted lawyer_dum* if court_name=="AARHUS" , cov(aardum* `Xvar_short') fitweight(1) 	numknots(3) 
	    putexcel C`j' =(r(pval))
		    putexcel C`d' =(r(df))
	    putexcel C`f' =(r(chi2))
	  
  testjfe `var' granted lawyer_dum* if court_name=="AAlBORG", cov(aardum* `Xvar_short') fitweight(1) numknots(3)
    putexcel D`j' =(r(pval))
	    putexcel D`d' =(r(df))
	    putexcel D`f' =(r(chi2))
	  
  testjfe `var' granted lawyer_dum* if court_name=="ODENSE" , cov(aardum* `Xvar_short') fitweight(1)	numknots(3) 
    putexcel E`j' =(r(pval))
	    putexcel E`d' =(r(df))
	    putexcel E`f' =(r(chi2))
	  
  testjfe `var' granted lawyer_dum* if court_name=="ROSKILDE" , cov(aardum* `Xvar_short') fitweight(1) 	numknots(3) 
    putexcel F`j' =(r(pval))
	    putexcel F`d' =(r(df))
	    putexcel F`f' =(r(chi2))
	  
  testjfe `var' granted lawyer_dum* if court_name=="HJORRING" , cov(aardum* `Xvar_short') fitweight(1) 	numknots(3) 
    putexcel G`j' =(r(pval))
	      putexcel G`d' =(r(df))
	    putexcel G`f' =(r(chi2))
		
  testjfe `var' granted lawyer_dum* if court_name=="RANDERS", cov(aardum* `Xvar_short') fitweight(1) numknots(3) 
    putexcel H`j' =(r(pval))
	    putexcel H`d' =(r(df))
	    putexcel H`f' =(r(chi2))
	  
  testjfe `var' granted lawyer_dum* if court_name=="HERNING" , cov(aardum* `Xvar_short') fitweight(1) 	numknots(3) 
    putexcel I`j' =(r(pval))
	    putexcel I`d' =(r(df))
	    putexcel I`f' =(r(chi2))
	  
  testjfe `var' granted lawyer_dum* if  court_name=="HORSENS" , cov(aardum* `Xvar_short') fitweight(1) numknots(3) 
    putexcel J`j' =(r(pval))
	    putexcel J`d' =(r(df))
	    putexcel J`f' =(r(chi2))
	  
  testjfe `var' granted lawyer_dum* if court_name=="SVENDBORG", cov(aardum* `Xvar_short') fitweight(1) numknots(3) 
  putexcel K`j' =(r(pval))
      putexcel K`d' =(r(df))
	    putexcel K`f' =(r(chi2))

local f =`f'+1
putexcel A`f'="number of cases"
putexcel B`f'=4053
putexcel C`f'=2670
putexcel D`f'=1476
putexcel E`f'=1365
putexcel F`f'=1241
putexcel G`f'=993
putexcel H`f'=853
putexcel I`f'=824
putexcel J`f'=669
putexcel K`f'=629


	}
  
